from typing import List, Optional
from uuid import UUID
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import select, delete
from sqlalchemy.orm import selectinload

from models.document.document_compliance_relation import DocumentComplianceRelation
from schemas.document.document_compliance_relation import DocumentComplianceRelationCreate


async def get_relation_by_id(db: AsyncSession, relation_id: UUID) -> Optional[DocumentComplianceRelation]:
    """根据ID获取关联关系"""
    result = await db.execute(
        select(DocumentComplianceRelation)
        .options(
            selectinload(DocumentComplianceRelation.document),
            selectinload(DocumentComplianceRelation.compliance_item)
        )
        .where(DocumentComplianceRelation.id == relation_id)
    )
    return result.scalar_one_or_none()


async def create_relation(db: AsyncSession, relation: DocumentComplianceRelationCreate) -> DocumentComplianceRelation:
    """创建文档与检查项的关联"""
    db_relation = DocumentComplianceRelation(**relation.model_dump())
    db.add(db_relation)
    await db.commit()
    await db.refresh(db_relation)
    return db_relation


async def delete_relation(db: AsyncSession, relation_id: UUID) -> bool:
    """删除关联关系"""
    result = await db.execute(
        delete(DocumentComplianceRelation).where(DocumentComplianceRelation.id == relation_id)
    )
    await db.commit()
    return result.rowcount > 0


async def get_document_compliance_relations(db: AsyncSession, document_id: UUID) -> List[DocumentComplianceRelation]:
    """获取文档关联的所有检查项"""
    result = await db.execute(
        select(DocumentComplianceRelation)
        .options(selectinload(DocumentComplianceRelation.compliance_template_item))
        .where(DocumentComplianceRelation.document_id == document_id)
        .order_by(DocumentComplianceRelation.created_at)
    )
    return result.scalars().all()


async def get_compliance_template_item_documents(db: AsyncSession, compliance_template_item_id: UUID) -> List[DocumentComplianceRelation]:
    """获取模板项关联的所有文档"""
    result = await db.execute(
        select(DocumentComplianceRelation)
        .options(selectinload(DocumentComplianceRelation.document))
        .where(DocumentComplianceRelation.compliance_template_item_id == compliance_template_item_id)
        .order_by(DocumentComplianceRelation.created_at)
    )
    return result.scalars().all()


async def check_relation_exists(db: AsyncSession, document_id: UUID, compliance_template_item_id: UUID) -> bool:
    """检查文档与模板项的关联是否已存在"""
    result = await db.execute(
        select(DocumentComplianceRelation)
        .where(
            DocumentComplianceRelation.document_id == document_id,
            DocumentComplianceRelation.compliance_template_item_id == compliance_template_item_id
        )
    )
    return result.scalar_one_or_none() is not None


async def delete_document_relations(db: AsyncSession, document_id: UUID) -> int:
    """删除文档的所有关联关系"""
    result = await db.execute(
        delete(DocumentComplianceRelation).where(DocumentComplianceRelation.document_id == document_id)
    )
    await db.commit()
    return result.rowcount


async def delete_compliance_template_item_relations(db: AsyncSession, compliance_template_item_id: UUID) -> int:
    """删除模板项的所有关联关系"""
    result = await db.execute(
        delete(DocumentComplianceRelation).where(DocumentComplianceRelation.compliance_template_item_id == compliance_template_item_id)
    )
    await db.commit()
    return result.rowcount


async def get_relations_by_type(db: AsyncSession, relation_type: str) -> List[DocumentComplianceRelation]:
    """根据关联类型获取关联关系"""
    result = await db.execute(
        select(DocumentComplianceRelation)
        .options(
            selectinload(DocumentComplianceRelation.document),
            selectinload(DocumentComplianceRelation.compliance_item)
        )
        .where(DocumentComplianceRelation.relation_type == relation_type)
        .order_by(DocumentComplianceRelation.created_at)
    )
    return result.scalars().all()


async def batch_create_relations(db: AsyncSession, relations: List[DocumentComplianceRelationCreate]) -> List[DocumentComplianceRelation]:
    """批量创建关联关系"""
    db_relations = []
    for relation in relations:
        # 检查关联是否已存在
        exists = await check_relation_exists(db, relation.document_id, relation.compliance_template_item_id)
        if not exists:
            db_relation = DocumentComplianceRelation(**relation.model_dump())
            db.add(db_relation)
            db_relations.append(db_relation)
    
    if db_relations:
        await db.commit()
        for relation in db_relations:
            await db.refresh(relation)
    
    return db_relations
